package com.testAll;

import com.core.comment.StringUtil;
import com.core.jdbc.SqlHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class poiTest2 {
    public static void main(String[] args) throws SQLException {
        testSql();
    }

    public static void testSql() throws SQLException {
        String sql = "SELECT kecheng,COUNT(1) from xxxjiaoan2 GROUP BY kecheng HAVING COUNT(1)=17;";
        System.out.println(sql);
        List<Map<String, Object>> list = SqlHelper.queryMapList(sql, new HashMap<>());
        String r = "";
        for (Map<String, Object> map : list) {
            if (StringUtil.isBlack(r)) {
                r = r + "'"+map.get("kecheng").toString()+"'";
            } else {
                r = r + "," + "'"+map.get("kecheng").toString()+"'";
            }

        }
        System.out.println(r);
        //SqlHelper.sqlOperateList(list);
    }

    public  static  String hujieTrue="";
    public  static  String hujie2True="";
    public  static  String jiaojuTrue="";
    public  static  String fangfaTrue="";

    public  static  int idTrue=0;
    public  static  String kechengTrue="";

    public static List<String> parseExcel(String path) throws SQLException {
        List<String> list = new ArrayList<>();
        try {
            //1、获取文件输入流
            InputStream inputStream = new FileInputStream(path);
            //2、获取Excel工作簿对象
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

            int num = workbook.getNumberOfSheets();
            for (int i = 0; i < num; i++) {
                //3、得到Excel工作表对象
                XSSFSheet sheetAt2 = workbook.getSheetAt(i);
                String sheetName = sheetAt2.getSheetName();
                //4、循环读取表格数据
                int start = 0;
                for (Row row : sheetAt2) {
                    //首行（即表头）不读取
                    if (row.getRowNum() <= 1) {
                        start++;
                        continue;
                    }
                    start++;

//                    if(start==12){
//                        ++start;
//                    }

//                    if(start==20){
//                        ++start;
//                    }



                    //读取当前行中单元格数据，索引从0开始
                    String huanjie = row.getCell(0) != null ? row.getCell(0).getStringCellValue() : null;
                    String huanjie2 = row.getCell(1) != null ? row.getCell(1).getStringCellValue() : null;
                    //String shijian = row.getCell(2) != null ? row.getCell(2).getStringCellValue() : null;
                    String mubiao = row.getCell(2) != null ? row.getCell(2).getStringCellValue() : null;
                    String jiaoju = row.getCell(3) != null ? row.getCell(3).getStringCellValue() : null;
                    String fangfa = row.getCell(4) != null ? row.getCell(4).getStringCellValue() : null;

                    //System.out.println(huanjie + "  "+huanjie2+" " + shijian + " " + mubiao + " " + jiaoju + " " + fangfa);

                    if(StringUtil.isBlack(huanjie)&&StringUtil.isBlack(huanjie2)){
                        huanjie=hujieTrue;
                        huanjie2=hujie2True;
                        jiaojuTrue=jiaojuTrue+"\n"+jiaoju;
                        fangfaTrue=fangfaTrue+"\n"+fangfa;
                    }else {
                        hujieTrue=huanjie;
                        hujie2True=huanjie2;
                        jiaojuTrue=jiaoju;
                        fangfaTrue=fangfa;
                        idTrue=start;
                        kechengTrue=sheetName;
                    }

                    String sql = " INSERT INTO xxxjiaoan2 VALUES ('" + start + "', '" + sheetName + "', '" + huanjie + "', '" + huanjie2 + "', null, '" + mubiao + "', '" + jiaoju + "', '" + fangfa + "', null); ";
                    System.out.println(sql);

                    list.add(sql);

                    String sql2="UPDATE xxxjiaoan2 set  jiaoju='"+jiaojuTrue+"' ,fangfa='"+fangfaTrue+"' where id="+idTrue+" and kecheng='"+sheetName+"';";
                    list.add(sql2);
                }
            }

            //5、关闭流
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

        //SqlHelper.sqlOperateList(list);
        return list;
    }
}

/*
if(start==10||start==18||start==20){
                            ++start;
                        }
 */
